#!/usr/bin/python
#coding=utf-8

from model.dbutils import DBUtils
import MySQLdb
from model.product import Product
from model.user import User 
from MySQLdb.cursors import DictCursor
from igor import logging


class OrderState(object):

    ##
    #定义购买状态:包括购买成功，产品已经卖出，
    #价格已经更新，余额不足,订单找不到，其他错误。
    ##
    (OK, SOLD_OUT, PRICE_UPDATED, LESS_MONEY, 
    NOTFOUND, OTHER_ERR) = range(6)


class SaleOrder(object):
    
    def __init__(self, oid=None, user=None, product=None, 
            odate = None, status=0, price=None, contact=None) :
        self.oid = oid 
        self.user = user
        self.product = product
        self.odate = odate
        self.status = status
        self.price = price 
        self.contact = contact 


class OrderManager(object):

    def __init__(self):
        self.dbutils = DBUtils()

    ##
    #根据不同的情形返回不同状态:
    #SOLD_OUT:该商品已经卖出
    #PRICE_UPDATED:该商品价格已经修改
    #LESS_MONEY:余额不足,但是下订单成功，订单状态为未支付。
    #OTHER_ERR:其他错误。
    #OK:购买成功
    ##
    def buy(self, pid, user, price):
        conn, cur = None, None
        sql_product = """
            SELECT price FROM product WHERE id=%s 
            AND status=0 FOR UPDATE 
            """
        sql_product_update = """
            UPDATE product set status=1 where id=%s
            """
        sql_order = """
            INSERT INTO salesorder(pid, uid, price, contact, status) 
            VALUES(%s, %s, %s, %s, %s) 
            """
        try:
            price = float(price) 
            conn = self.dbutils.connect()
            cursor = conn.cursor(DictCursor)
            cursor.execute(sql_product, pid)
            row = cursor.fetchone()
            if not row:
                conn.rollback()
                return OrderState.SOLD_OUT 
            price_db = float(row['price'])
            contact = user.addr + '|' + user.name + ' ' + user.tel
            logging.log('price=%f, price_db=%f' % (price, price_db))
            if price != price_db:
                conn.rollback()
                return OrderState.PRICE_UPDATED 
            
            ##更新产品的状态
            cursor.execute(sql_product_update, pid)

            ##插入订单项
            cursor.execute(sql_order, (pid, user.uid, price, contact, 0))
            order_id = conn.insert_id()
            conn.commit()
        except MySQLdb.Error, err:
            conn.rollback()
            logging.log(err)
            return OrderState.OTHER_ERR
        except Exception, err:
            conn.rollback()
            logging.log(err)
            return OrderState.OTHER_ERR 
        finally:
            self.dbutils.close(conn, cur)
        return self.pay(order_id);

    def pay(self, order_id):
        conn, cur = None, None
        sql_order = """
            SELECT s.price, s.uid as buy_uid, p.uid as sell_uid, pid  
            FROM salesorder s, product p 
            WHERE s.pid = p.id and s.id=%s 
            FOR UPDATE;
            """
        sql_user = """
            SELECT money FROM user WHERE
            id=%s FOR UPDATE
            """
        sql_user_update = """
            UPDATE user set money = %s where id=%s
            """
        sql_order_update = """
            UPDATE salesorder SET status=1 
            WHERE id=%s 
            """
        try:
            ##
            #查询订单价格和买卖双方的id并锁定订单
            ##
            conn = self.dbutils.connect()
            cursor = conn.cursor(DictCursor)
            cursor.execute(sql_order, order_id)
            row = cursor.fetchone()
            if row:
                price = float(row['price'])
                buy_uid = row['buy_uid']
                sell_uid = row['sell_uid']
            else:
                conn.rollback()
                return OrderState.NOTFOUND

            ##
            #查询买家余额并锁定
            ##
            cursor.execute(sql_user, buy_uid)
            row = cursor.fetchone()
            buy_money = float(row['money'])
            if buy_money < price:
                conn.rollback()
                return OrderState.LESS_MONEY 

            ##
            #查询卖家余额并锁定
            ##
            cursor.execute(sql_user, sell_uid)
            row = cursor.fetchone()
            sell_money = float(row['money'])

            ##
            #更新买家和卖家的账户余额
            ##
            users = [] 
            users.append((buy_money-price, buy_uid))
            users.append((sell_money+price, sell_uid))
            cursor.executemany(sql_user_update, users)

            #更新订单
            cursor.execute(sql_order_update, order_id)
            conn.commit()
        except MySQLdb.Error, err:
            conn.rollback()
            logging.log(err)
            return OrderState.OTHER_ERR 
        except Exception, err:
            conn.rollback()
            logging.log(err)
            return OrderState.OTHER_ERR 
        finally:
            self.dbutils.close(conn, cur)
        return OrderState.OK 

    def query_orders_buy(self, uid):
        sql = """
            SELECT p.id AS pid, p.name AS pname, p.descpt, p.status AS pstatus,
                p.price AS pprice, pdate, u.name AS uname, u.tel, u.addr, 
                u.postcode, s.odate, s.id AS sid, s.status AS sstatus, 
                s.price AS sprice, s.contact
            FROM product p, salesorder s, user u 
            WHERE p.id=s.pid AND p.uid=u.id AND s.uid=%s
            ORDER BY s.odate DESC
            """
        rows = self.dbutils.execute_dict(sql, uid)
        orders = []
        if rows:
            for row in rows:
                product = Product()
                product.pid = row['pid']
                product.name = row['pname']
                product.price = row['pprice']
                product.descpt = row['descpt']
                product.status = row['pstatus']
                product.pdate = row['pdate']
                sell_user = User()
                sell_user.name = row['uname']
                sell_user.addr = row['addr']
                sell_user.tel = row['tel']
                sell_user.postcode = row['postcode']
                order_id = row['sid']
                order_date = row['odate']
                order_price = row['sprice']
                order_contact = row['contact']
                order_status = row['sstatus']
                order = SaleOrder(user=sell_user, product=product, oid=order_id, 
                    odate = order_date, price=order_price, status=order_status, 
                    contact=order_contact)
                orders.append(order)
        return orders	

    def query_orders_sell(self, uid):
        sql = """
            SELECT p.id AS pid, p.name AS pname, p.price AS pprice, p.descpt, 
                p.status AS pstatus, p.pdate, s.uid AS suid, s.id AS sid, s.odate, 
                s.status AS sstatus, s.price as sprice,  s.contact  
            FROM product p LEFT JOIN salesorder s ON p.id=s.pid  
            WHERE p.uid=%s  ORDER BY odate DESC;
            """
        rows = self.dbutils.execute_dict(sql, uid)
        orders = []
        if rows:
            for row in rows:
                product = Product()
                product.pid = row['pid']
                product.name = row['pname']
                product.price = row['pprice']
                product.descpt = row['descpt']
                product.status = row['pstatus']
                product.pdate = row['pdate']
                order_id = row['sid']
                order_date = row['odate']
                order_price = row['sprice']
                order_contact = row['contact']
                order_status = row['sstatus']
                order = SaleOrder(product=product, oid=order_id, 
                    odate = order_date, price=order_price, status=order_status, 
                    contact=order_contact)
                orders.append(order)
        return orders	
            
            


        

